import pymysql.cursors
import pandas as pd
from HttpService import IsTest

host = "192.168.1.129"
port = 3306
dbname = "bi_info"
uname = "java_ai_sel"
upwd = "lzhit282t7"

formalHost = "192.168.0.24"
formalPort = 3306
formalDBName = "bi_update_info"
formalUName = "bi_program"
formalUPwd = "tyklxn&hu"
if IsTest:
    pass
else:
    host = formalHost
    port = formalPort
    dbname = formalDBName
    uname = formalUName
    upwd = formalUPwd


datasql = "select * from ai_template_model_list"
dictsql = "select * from ai_template_keywords"
aidatasql = "select * from ai_template_model"
aidatatitlesql = "select tb1.COLUMN_NAME as '列名称' from information_schema.COLUMNS tb1 where  tb1.TABLE_SCHEMA='bi_update_info' and tb1.TABLE_NAME='ai_template_model'"
if IsTest:
    aidatatitlesql = "select tb1.COLUMN_NAME as '列名称' from information_schema.COLUMNS tb1 where  tb1.TABLE_SCHEMA='bi_info' and tb1.TABLE_NAME='ai_template_model'"
aidatarowsSQL = "select template_id from ai_template_model group by template_id"
aidatarowscountSQL = "select count(template_id) from (%s)t" % aidatarowsSQL
aidataassigntemplateidsSQL = "select * from ai_template_model where %s"

def connectiondb():
    connection = pymysql.connect(host=host,
                             port=port,
                             user=uname,
                             password=upwd,
                             db=dbname,
                             charset='utf8')
    return connection

def queryWithEnv (action):
    conn = connectiondb()
    try:
        with conn.cursor() as cursor:
            def _query (sql):
                cursor.execute(sql)
                result = cursor.fetchall()
                return result
            action (_query)
    finally:
        conn.close()

def query(sql, action):
    conn = connectiondb()
    try:
        with conn.cursor() as cursor:
            cursor.execute(sql)
            result = cursor.fetchall()
            action (result)
    finally:
        conn.close()

def aidata():
    global aidatasql, aidatatitlesql

    data = []
    def _handledata(result):
        nonlocal data
        data=list(result)

    query(aidatasql,_handledata)

    title=[]
    def _handletitle(result):
        nonlocal title
        for p in result:
            title.append(p[0])
    query(aidatatitlesql, _handletitle)
    return pd.DataFrame(data=data, columns=title)

def data():
    global datasql
    excel = None
    def _handle(result):
        nonlocal excel
        array = []
        for item in result:
            array.append(item)
        excel = pd.DataFrame (data=array)

    query(datasql,_handle)
    return excel

def dict():
    global dictsql
    excel = None
    def _handle(result):
        nonlocal excel
        array = []
        for item in result:
            array.append (item)
        excel = pd.DataFrame(data=array)

    query(dictsql, _handle)

    return excel

def aidataassignids(templateids):
    global aidataassigntemplateidsSQL
    wheresql = " or template_id=".join(templateids)
    wheresql = "template_id="+wheresql
    sql = aidataassigntemplateidsSQL % wheresql
    data=[]
    def _handledata(result):
        nonlocal data
        data = list(result)
    query(sql, _handledata)

    title = []

    def _handletitle(result):
        nonlocal title
        for p in result:
            title.append(p[0])

    query(aidatatitlesql, _handletitle)
    return pd.DataFrame(data=data, columns=title)

def GetAIDataWithAssignIds (action):
    global aidataassigntemplateidsSQL,aidatatitlesql
    def _action (_query):
        def _result (templateids):
            wheresql = " or template_id=".join(templateids)
            wheresql = "template_id=" + wheresql
            sql = aidataassigntemplateidsSQL % wheresql
            data = list(_query(sql))

            title = []
            for p in _query(aidatatitlesql):
                title.append(p[0])
            return pd.DataFrame(data=data, columns=title)
        if action:
            action(_result)
    queryWithEnv(_action)

def aidatatemplateids(limit = -1, limitNum = -1):
    global aidatarowsSQL
    sql = aidatarowsSQL
    if limit >= 0:
        sql += " limit " + str(limit)
        if limitNum > 0:
            sql += "," + str(limitNum)
    data = set()
    def _hanlde(result):
        nonlocal data
        for ret in result:
            data.add(str(ret[0]))
    query(sql,_hanlde)
    return data

def aidatatemplateCount():
    global aidatarowscountSQL
    sql = aidatarowscountSQL
    count = 0
    def _handle(result):
        nonlocal count
        count = result[0][0]
    query(sql, _handle)

    return count

# p = set()
# p.add("1000000011673")
# p.add("1000000011674")
# p.add("1000000014411")
# p.add("1000000016266")
# print(aidataassignids(p))
# print(aidatatemplateCount())
# query(aidataRowsSQL,lambda x:print(x))
